﻿using System;
using System.Reflection;
using System.Text;
using System.IO;
using System.Data.OleDb;
using System.Data;

namespace BinDataTool
{
    class BinJson
    {
        // const字符串
        public static string strEnd = " \r\n";
        public static string strXlsx = ".xlsx";
        public static string strDat = ".json";

        // const数值
        public const int nUser = 0;    // 第1行 字段用户是客户端or服务器or公用
        public const int nName = 1;    // 第2行 字段名字
        public const int nNote = 2;    // 第3行 注释
        public const int nType = 3;    // 第4行 字段数据类型   
        public const int nStartRow = 4;   // 第6行， 从该行开始读取

        public static string NowTime() { return DateTime.Now.ToString() + " "; }

        // Excel相关对象
        public static OleDbConnection gConnection;
        public static DataSet gSheetData;
        public static int nSheetIndex;

        // 打开工作薄
        public static void GetBook(string szName)
        {
            string source = "Provider = Microsoft.Ace.OLEDB.12.0; Data Source = '" + szName + "'; Extended Properties = 'Excel 12.0;HDR=NO;IMEX=1'";
            gConnection = new OleDbConnection(source);
        }

        // 打开指定Worksheet
        public static bool GetSheet()
        {
            string select = "SELECT * FROM [sheet1$]";
            OleDbDataAdapter readCommand = new OleDbDataAdapter(select, gConnection);
            gSheetData = new DataSet();
            readCommand.Fill(gSheetData);
            return true;
        }

        // 行数
        public static int Rows()
        { return gSheetData.Tables[0].Rows.Count; }

        // 列数
        public static int Cols()
        { return gSheetData.Tables[0].Columns.Count; }

        // 分割字符串
        public static String[] SubString(String strData, char separator)
        {
            String[] strArray = strData.Split(separator);
            return strArray;
        }

        public static string GetString(int i, int j)
        {
            if (null == gSheetData.Tables[0].Rows)
            { return ""; }
            return gSheetData.Tables[0].Rows[i][j].ToString();
        }

        // 解析整型字段
        public static Int32 ReadInt32(int i, int j)
        {
            if (null == gSheetData.Tables[0].Rows)
            { return 0; }
            string str = gSheetData.Tables[0].Rows[i][j].ToString();
            if (str == "")
                return 0;
            return System.Convert.ToInt32(str);
        }

        public static Int64 ReadInt64(int i, int j)
        {
            if (null == gSheetData.Tables[0].Rows)
            { return 0; }
            string str = gSheetData.Tables[0].Rows[i][j].ToString();
            if (str == "")
                return 0;
            return System.Convert.ToInt64(str);
        }

        // 解析float
        public static float ReadFloat(int i, int j)
        {
            if (null == gSheetData.Tables[0].Rows)
            { return 0; }
            string str = gSheetData.Tables[0].Rows[i][j].ToString();
            if (str == "")
                return 0;
            return System.Convert.ToSingle(str);
        }

        // 解析字符串
        public static byte[] ReadString(int i, int j)
        {
            object element = gSheetData.Tables[0].Rows[i][j];
            if (null == element)
            { return Encoding.UTF8.GetBytes(""); }
            return Encoding.UTF8.GetBytes(element.ToString());
        }

        // 开始解析Excel
        private static void StartParse(string path)
        {
            nSheetIndex = 1;
            GetBook(MeFile.GetFilePath(path));
            GetSheet();
        }

        // 结束解析Excel
        public static void EndParse()
        {
            nSheetIndex = 1;
            gConnection = null;
            gSheetData = null;
        }

        public static string ParseNum( int row, int col )
        {
            return gSheetData.Tables[0].Rows[row][col].ToString();           
        }

        public static string ParseBool(int row, int col)
        {            
            string value = gSheetData.Tables[0].Rows[row][col].ToString().ToUpper();
            if (value.Equals("1"))
            { return "true"; }
            else
            { return "false"; }
        }
        public static string ParseNumArray(int row, int col)
        {
            string strTemp = gSheetData.Tables[0].Rows[row][col].ToString().Replace("\n", "");
            if (strTemp.Trim().Length <= 0)
            {
                return "null";
            }

            string strData = "[\r\n";
            strData += "            ";
            bool endwithsem = false;
            int lastdel = strTemp.LastIndexOf(';');
            if (lastdel == strTemp.Length - 1)
            {
                endwithsem = true;
            }
            else
            {
                endwithsem = false;
            }

            for (int i = 0; i < strTemp.Length; ++i )
            {
                if (strTemp[i] == ';')
                {
                    if (endwithsem && i == lastdel)
                    {
                        
                    }
                    else
                    {
                        strData += ",\r\n";
                        strData += "            ";    
                    }
                }
                else
                {
                    strData += strTemp[i];
                }
            }

            strData += "\r\n         ]";
            return strData;
        }

        public static string ParseString( int row, int col )
        {
            string strData = "\"";
            strData += gSheetData.Tables[0].Rows[row][col].ToString() + "\"";

            return strData;
        }

        public static string ParseStrArray(int row, int col)
        {
            string strData = "[\r\n";
            string strTemp = gSheetData.Tables[0].Rows[row][col].ToString().Replace("\n", "");
            if (strTemp.Trim().Length <= 0)
            {
                return "null";
            }

            string[] strArray = SubString(strTemp, ';');
            for (int i = 0; i < strArray.Length; ++i)
            {
                if (strArray[i].Trim().Length > 0)
                {
                    if (i != 0)
                    {
                        strData += ",\r\n";
                    }
                    
                    strData += "            \"" + strArray[i] + "\"";                                        
                }
            }

            strData += "\r\n         ]";
            return strData;
        }

//        public static string ParseNumMap(int row, int col)
//        {
//            string strData = "{\r\n";
//            string strTemp = gSheetData.Tables[0].Rows[row][col].ToString().Replace("\n", "");
//            if (strTemp.Trim().Length <= 0)
//            {
////                 strData += "       }\r";
////                 return strData;
//                return "null";
//            }

//            string[] strArray = SubString(strTemp, ';');

//            for (int i = 0; i < strArray.Length; ++i )
//            {
//                string[] str = SubString(strArray[i], ',');

//                strData += "            \"" + str[0] + "\":" + str[1];
//                if ( i != strArray.Length - 1 )
//                { strData += ","; }
                
//                strData += "\r\n";

//                if ( i == strArray.Length - 1 )
//                { strData += "          }"; }
//            }

//                //
//            return strData;
//        }

        //public static string ParseStrMap(int row, int col)
        //{
        //    string strData = "{\r\n";
        //    string strTemp = gSheetData.Tables[0].Rows[row][col].ToString().Replace("\n", "");
        //    if (strTemp.Trim().Length <= 0)
        //    {
        //        //                 strData += "       }\r";
        //        //                 return strData;
        //        return "null";
        //    }

        //    string[] strArray = SubString(strTemp, ';');

        //    for (int i = 0; i < strArray.Length; ++i)
        //    {
        //        string[] str = SubString(strArray[i], ',');
        //        if (str.Length != 2)
        //        {
        //            continue;
        //        }

        //        strData += "            \"" + str[0] + "\":\"" + str[1] + "\"";
        //        if (i != strArray.Length - 1)
        //        { strData += ","; }

        //        strData += "\r\n";

        //        if (i == strArray.Length - 1)
        //        { strData += "          }"; }
        //    }

        //    //strData += "       }\r\n";
        //    return strData;
        //}

        public static bool ParseExcel( string fileName, System.Windows.Forms.TextBox txtBox)
        {
            bool bHasData = false;
            string strJsonHead = "{\r\n";                   //{
            string strJsonName = " \"ID2Desc\":\r\n";       // "ID2Desc":

            string strJsonDataBegin = " {\r\n";             // {

            string strJsonData = "";

            string strJsonDataEnd = " }\r\n";               // }

            string strJsonTail = "}";                       //}
            string strJsonFile = "";                        //

            StartParse(fileName);
            object element = null;
            string[] strUser = new string[Cols()];
            string[] strType = new string[Cols()];
            string[] strName = new string[Cols()];
            for (int i = 0; i < Cols(); ++i)
            {
                element = gSheetData.Tables[0].Rows[nUser][i];
                if (null == element)
                { return false; }
                strUser[i] = element.ToString();

                if ("#" == strUser[i])
                {
                    strType[i] = "";
                    strName[i] = "";
                }
                else
                {
                    element = gSheetData.Tables[0].Rows[nType][i];
                    if (null == element)
                    { return false; }
                    strType[i] = element.ToString();

                    element = gSheetData.Tables[0].Rows[nName][i];
                    if (null == element)
                    { return false; }
                    strName[i] = element.ToString();
                }
            }
            // 数据从第5行开始
            for (int i = nStartRow; i < Rows(); ++i)
            {
                int nDataRow = i - 3;

                if (Rows() != 5)  // 只有一行数据
                {
                    strJsonData += "    \"" + nDataRow.ToString() + "\":\r\n";      //"1":
                    strJsonData += "    {\r\n";
                }

                for (int j = 0; j < Cols(); ++j)
                {
                    // 一个字段
                    switch (strUser[j])
                    {
                        case "#":
                            break;
                        case "c":
                        case "a":
                            {
                                bHasData = true;
                                strJsonData += "        \"" + strName[j] + "\":";
                                switch (strType[j])
                                {
                                    case "int":
                                        {
                                            strJsonData += ParseNum(i, j);
                                        }
                                        break;
                                    case "intarray":
                                        {
                                            strJsonData += ParseNumArray(i, j);
                                        }
                                        break;
                                    case "int64":
                                        {
                                            strJsonData += ParseNum(i, j);
                                        }
                                        break;
                                    case "int64array":
                                        {
                                            strJsonData += ParseNumArray(i, j);
                                        }
                                        break;
                                    case "string":
                                        {
                                            strJsonData += ParseString(i, j);
                                        }
                                        break;
                                    case "stringarray":
                                        {
                                            strJsonData += ParseStrArray(i, j);
                                        }
                                        break;
                                    default:
                                        {
                                            txtBox.Text += "数据类型错误 : " + strType[j] + ServerParser.strEnd;
                                            return false;
                                        }
                                        break;
                                }
                                if (j != Cols() - 1)
                                {
                                    strJsonData += ",";
                                }
                                strJsonData += "\r\n";
                            }
                            break;
                        default:
                            break;
                    }
                }

                if (Rows() != 5) // 只有一行数据
                {
                    strJsonData += "    }";
                    if (i != Rows() - 1)
                    {
                        strJsonData += ",";
                    }
                }


                strJsonData += "\r\n";
            }

            if ( !bHasData )
            { return true; }

            if (Rows() == 5)
            { strJsonFile += strJsonDataBegin + strJsonData + strJsonDataEnd; }
            else
            { strJsonFile += strJsonHead + strJsonName + strJsonDataBegin + strJsonData + strJsonDataEnd + strJsonTail; }

            string fPath = System.Environment.CurrentDirectory + @"\dat_client\";
            FileStream wFile = new FileStream(fPath + fileName + ".json", FileMode.Create, FileAccess.Write);
            byte[] filedata = Encoding.UTF8.GetBytes(strJsonFile);
            wFile.Write(filedata, 0, filedata.Length);
            wFile.Flush();
            wFile.Close();

            return true;
        }
    }
}